sdselect command WHERE clause operations

You can use an optional WHERE clause to compare fields in an sdselect search.

Using the WHERE clause to compare fields

The optional sdselect WHERE clause uses the same expression syntax as the eval command. This means that the WHERE clause interprets quoted strings as literals, and if strings are not quoted, the WHERE clause interprets those strings as field names. This means you can use the sdselect WHERE clause to compare 2 different fields.

For example, this search counts events where the field ipaddress is equal to the field clientip:

| sdselect count FROM fed_index_1 WHERE ipaddress=clientip

Meanwhile, this search counts events where the value of the field ipaddress is the literal string clientip:

| sdselect count FROM fed_index_1 WHERE ipaddress="clientip"

Evaluation order for the WHERE clause

The sdselect WHERE clause evaluates Boolean operators in the following order:

1. Expressions within parentheses
2. NOT clauses
3. AND clauses
4. OR clauses

Apply date and time evaluation functions to fields in the WHERE clause

If you use the optional WHERE clause in an sdselect search, you can apply the relative_time(), strftime(), and strptime() date and time evaluation functions to declared and undeclared time fields, constant values, and other evaluation functions. If you have time fields that have values in the SQL timestamp data type, you can also use timestamp_from_unixtime and timestamp_to_unixtime to compare time field values that have the SQL timestamp data type with numeric UNIX time format values.

The following subsections provide more information about these elements, and offer examples that show how you apply them to the date and time evaluation functions in sdselect WHERE clauses.

Declared time field element

A declared time field is a time field that is declared as a Time field, Unix time field, or Time partition field in the definition of the federated index that you reference in the search.

• If you use Federated Search for Amazon S3 and are searching a federated index that maps to a customer-created AWS Glue table dataset, you declare these field values when you define the federated index. Time field and Time partition field values must be field names in the AWS Glue table dataset to which the federated index maps. See Map a federated index to a customer-created AWS Glue Data Catalog table dataset.
• If you use Federated Search for Amazon S3 and are searching a federated index that maps to a Splunk-managed AWS Glue table dataset, these values are set to defaults. See Map an Amazon S3 federated index to a Splunk-managed AWS Glue table dataset.
• If you use Federated Analytics, only the Time partition field is displayed in the federated index definition, and it defaults to time_dt. Federated Analytics searches use _time as a time field. See Map Amazon Security Lake federated indexes to AWS Glue tables.

In sdselect searches, there are some limitations to the declared time fields you can apply to date and time evaluation functions. See Limitations to the field and data types you can apply to date and time functions in the WHERE clause.

Here is an example of an sdselect search that uses a WHERE clause to apply the relative_time() function to a declared time field:

| sdselect count FROM fed_index_1 WHERE relative_time(_time, "+1mon") > now()

In this example, _time is the Unix time field for the fed_index_1 federated index.

Undeclared time field elements

Undeclared time fields are time fields that you have not declared in the Time field, Unix time field, or Time partition field settings in the definition of the federated index that you reference in the search, but which do exist as additional time fields in the AWS Glue table dataset that is mapped to by the federated index. Undeclared time fields can have numeric, string, or SQL timestamp data types.

In sdselect searches, there are some limitations to the undeclared time fields you can apply to date and time evaluation functions. See Limitations to the field and data types you can apply to date and time functions in the WHERE clause.

Here is an example of an sdselect search that uses a WHERE clause to apply the relative_time() function to an undeclared time field:

| sdselect count FROM fed_index_1 WHERE relative_time(startTime, "+1mon") > now()

In this example, startTime is a time field in the AWS Glue table dataset to which fed_index_1 maps. The startTime field has not been declared as a Time field or Time partition field.

Constant value elements

Constant values are specific UNIX time format numbers or strings that can be expressed as date and time format variables, representing a specific date.

In sdselect searches, you can apply constant values to all three date and time evaluation functions.

In the following search example, _time is the Unix time field for the fed_index_1 federated index. The search uses strptime to convert the string value into a numeric UNIX time format value so the search can compare the value against the _time values, which are also in numeric UNIX time format.

| sdselect count FROM fed_index_1 WHERE strptime("2023-04-24 17:48:25.000 -0700", "%Y-%m-%d %H:%M:%S.%Q %z"), "+1y") > _time

Evaluation functions that convert SQL timestamp data type values

You can use the WHERE clause in conjunction with the timestamp_from_unixtime() and timestamp_to_unixtime() evaluation functions to directly compare timestamps in the numeric UNIX time format with SQL timestamp data type values. These functions are useful when the declared Time field or Time partition field for your federated index has SQL timestamp data type values, because the sdselect command does not natively support the timestamp data type.

If you use Federated Analytics to search Amazon Security Lake datasets, each of your federated indexes has a Time partition field that defaults to time_dt, a field with SQL timestamp data type values. Use timestamp_from_unixtime() and timestamp_to_unixtime() to run sdselect searches that involve this field.

For more information about using time fields with SQL timestamp data type values in sdselect searches, see When time fields have values with the SQL timestamp data type.

For detail about the timestamp_from_unixtime() and timestamp_to_unixtime() evaluation functions, as well as example searches, see Evaluation functions specific to sdselect.

Other evaluation functions

You can apply the now(), strptime(), and tonumber() functions to relative_time() and strftime() functions. You can apply the strftime() and tostring() functions to strptime() functions.

In the following search example, _time is the Unix time field for the fed_index_1 federated index. The _time field is also the event start time. The undeclared time field endTime is the event end time. This search returns the count of events that completed within the last hour.

| sdselect count FROM fed_index_1 WHERE _time > relative_time(strptime(endTime, "%Y-%m-%d %H:%M:%S.%Q %z"), "-1h")

For more information about using the Time field and the Unix time field in your searches, see Use time fields in sdselect searches.

Limitations to the field and data types you can apply to date and time functions in the WHERE clause

There are a few limitations to the fields and data types you can apply to the relative_time(), strftime(), and strptime() functions in the WHERE clause. In the following table, a value of "Yes" means you can apply the field or data type to the <time> or <string> required by the function. The strptime() function cannot accept Unix time field values and undeclared fields with numeric data types because the function requires a value with a string data type.

Function	Time field	Unix time field	Partition time field	Undeclared time fields with a numeric data type	Undeclared time fields with a string data type	Undeclared time fields with a SQL timestamp data type
relative_time(<time>,<specifier>)	Yes	Yes	Yes	Yes, but you must use a valid UNIX time format value for relative_time.	Yes, but you must use a valid UNIX time format value for relative_time.	Yes
strftime(<time>,<format>)	Yes	Yes	Yes	Yes, but you must use a valid UNIX time format value for strftime.	Yes, but you must use a valid UNIX time format value for strftime.	Yes
strptime(<string>,<format>)	Yes	No	Yes	No	Yes	Yes

Specify a wildcard with the WHERE clause

Use the like() evaluation function to specify a wildcard in an sdselect WHERE clause. The percent ( % ) symbol is the wildcard you use with the like() function. For more about the like() function, see Comparison and conditional functions in the Splunk Cloud Platform Search Reference.

Filtering on partition keys for a federated index that maps to a Splunk-managed AWS Glue table dataset

This requirement applies specifically to users of Federated Search for Amazon S3.

If you invoke a federated index that maps to a Splunk-managed AWS Glue table dataset in your sdselect search, and the definition of that federated index uses a wildcard (*) in the AWS Account IDs field to indicate that it supports all available AWS account ID values, you must include a WHERE clause in that same sdselect search that filters results by pk_account_id, to identify precisely the AWS account ID partitions that are involved in the search.

Similarly, if you invoke a federated index that maps to a Splunk-managed AWS Glue table dataset in your sdselect search, and the definition of that federated index uses a wildcard (*) in the AWS Regions field to indicate that it supports all available AWS region values, you must include a WHERE clause in that same sdselect search that filters results by pk_region, to identify precisely the AWS region partitions that are involved in the search.

For example, say the federated index fed_index_2 maps to a Splunk-managed AWS Glue table. The fed_index_2 federated index definition uses a wildcard symbol to indicate that its AWS Glue table uses all available AWS account ID partitions in the AWS CloudTrail dataset that the Glue table is based on.

The following search references the fed_index_2 federated index, so it has to identify which of the AWS account partitions it is running over from the AWS CloudTrail dataset upon which its AWS Glue table is based. It indicates that it is specifically looking at data from the partition associated with AWS account ID 123456789012.

| sdselect count FROM fed_index_2 WHERE pk_account_id="123456789012"

See Map a federated index to a Splunk-managed AWS Glue table dataset.

See also

sdselect command

sdselect command overview

sdselect command syntax details

sdselect command usage

Use time fields in sdselect searches

Evaluation functions specific to sdselect

sdselect command examples for Amazon S3